package aes.server;

import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Vector;

import ocsf.server.ConnectionToClient;
import aes.general.Exam;
import aes.general.FileSend;
import aes.general.Msg;
import aes.general.Question;
import aes.general.StudentActiveExam;
import aes.general.UserEntity;

import com.mysql.jdbc.PreparedStatement;


/**
 * The Class ServerLogic.
 */
final public class ServerLogic {

	/** The dblogin. */
	public static String dblogin;
	
	/** The dbpass. */
	public static String dbpass;
	
	/** The server. */
	public static AesServer server;
	
	/** The std act exan. */
	private static ArrayList<StudentActiveExam> stdActExan=new ArrayList<StudentActiveExam>();
	

	/**
	 * Run login query. Checks the password with the Data Base.
	 *
	 * @param msg contains the query 
	 * @param client the client
	 */
	public static void RunLoginQuery(Object msg, ConnectionToClient client) {

		
		Statement stmt;
		ArrayList<String> arr = new ArrayList<String>();
		try {Class.forName("com.mysql.jdbc.Driver").newInstance();}
		catch (Exception ex) {/* handle the error */}
		
		try {
			Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/aes", dblogin, dbpass);
			System.out.println("SQL connection succeed");
			stmt = conn.createStatement();
			
			ResultSet rs = stmt.executeQuery(((Msg) msg).toString());
			
			if (rs == null || !rs.first()) 
			{
				arr.add("error");
				Msg rep1 = new Msg(arr, "100");
				try {
					client.sendToClient(rep1);
					conn.close();
					} catch (IOException e) {e.printStackTrace();}	
				rs.close();
			}

			else 
			{
				arr.add(rs.getString(1)); // id
				arr.add(rs.getString(2)); // Name
				arr.add(rs.getString(3)); // Lastname
				arr.add(rs.getString(4)); // password
				arr.add(rs.getString(5)); // phone
				arr.add(rs.getString(6)); // mail
				arr.add(rs.getString(7)); // type
				
				if (rs.getString(7).equals("s"))
				{
					StudentActiveExam sae = new StudentActiveExam(rs.getString(1), client);
					stdActExan.add(sae);
				}
					
				if (rs.getString(7).equals("t"))  
					;   //4to eto?
				{
					rs = stmt.executeQuery("select T.SubjectId, S.Sname from aes.teacher T, aes.subjects S Where T.SubjectId = S.SId and T.TID = "+rs.getString(1));
					while (rs.next())
						{
						arr.add(rs.getString("SubjectId"));
						arr.add(rs.getString("Sname"));
						}
				}
				
				Msg rep1 = new Msg(arr, "100");
				
				try {
					client.sendToClient(rep1);
					conn.close();
					} 
				catch (IOException e) {e.printStackTrace();}
				rs.close();
			}
		}

		catch (SQLException ex) {
			System.out.println("SQLException: " + ex.getMessage());
			System.out.println("SQLState: " + ex.getSQLState());
			System.out.println("VendorError: " + ex.getErrorCode());
		}

	}

	/**
	 * Inserting to date base a new question composed by the teacher.
	 * Add question query.
	 *
	 * @param msg contains the question and related information
	 * @param client the client
	 */
	public static void RunAddQuestionQuery(Object msg, ConnectionToClient client) {
		// TODO Auto-generated method stub
		System.out.println("400");
		Question newqst = (Question) ((Msg) msg).getMsg();
		ArrayList<String> ans;
		Statement stmt;
		Msg rep;
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
		} catch (Exception ex) {/* handle the error */
		}
		try {
			Connection conn = DriverManager.getConnection(
					"jdbc:mysql://localhost/aes", dblogin, dbpass);
			System.out.println("SQL connection succeed");
			stmt = conn.createStatement();
			String subQr = new String("select * from questions WHERE QId= "
					+ newqst.getQId());
			ResultSet rs = stmt.executeQuery(subQr);
			if (rs.first() || newqst.getQId().trim().length() != 5 || newqst.getSubject().trim().length() != 2 || (newqst.getCorrectAns() > 4 || newqst.getCorrectAns() < 0) 
					|| Integer.parseInt(newqst.getAuthor()) == 0 || newqst.getQuestion().trim().length() == 0 || newqst.getAnswer().size() != 4) {
				 rep = new Msg("Error", "400");
				rs.close();
			} else {
				String query = "INSERT INTO `aes`.`questions` (`QId`,`Question`,`Description`,`TId`,`Subject`,`Answer1`,`Answer2`,`Answer3`,`Answer4`,`CorrectAns`) VALUES (?,?,?,?,?,?,?,?,?,?)";
				PreparedStatement preparedStmt = (PreparedStatement) conn
						.prepareStatement(query);
				preparedStmt.setString(1, newqst.getQId());
				preparedStmt.setString(2, newqst.getQuestion());
				preparedStmt.setString(3, newqst.getDescription());
				preparedStmt.setInt(4, Integer.parseInt(newqst.getAuthor()));
				preparedStmt.setString(5, newqst.getSubject());
				ans = newqst.getAnswer();
				preparedStmt.setString(6, ans.get(0));
				preparedStmt.setString(7, ans.get(1));
				preparedStmt.setString(8, ans.get(2));
				preparedStmt.setString(9, ans.get(3));
				preparedStmt.setInt(10, newqst.getCorrectAns());
				preparedStmt.execute();
				rep = new Msg("Ok", "400");
				try {
					client.sendToClient(rep);
				} catch (IOException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				client.notify();
				conn.close();
			}
			try {
				client.sendToClient(rep);
				conn.close();
			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			client.notify();
		} catch (SQLException ex) {
			System.out.println("SQLException: " + ex.getMessage());
			System.out.println("SQLState: " + ex.getSQLState());
			System.out.println("VendorError: " + ex.getErrorCode());
		}
	}


	/**
	 * Run student get exam query for the student to start exam with the 
	 * activation code 
	 *
	 * @param msg the exam acrivation code
	 * @param client the client
	 */
	public static void RunStudentGetExamQuery(Object msg,
			ConnectionToClient client) {
		Statement stmt;
		ArrayList<String> arr = new ArrayList<String>();
		Msg reply;
		System.out.println("202");

		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
		} catch (Exception ex) {/* handle the error */
		}
		try {
			Connection conn = DriverManager.getConnection(
					"jdbc:mysql://localhost/aes", dblogin, dbpass);
			System.out.println("SQL connection succeed");

			stmt = conn.createStatement();
			ResultSet rs = stmt
					.executeQuery("select examid from activatedexam WHERE ActivationCode="
							+ "\"" + ((Msg) msg).getMsg().toString() + "\"");

			if (rs == null || !rs.first()) {
				arr.add("error");
				reply = new Msg(arr, "202");
				try {
					client.sendToClient(reply);
					conn.close();
				} catch (IOException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				// client.notify();
				rs.close();
			} else {
				reply = new Msg(new String("Ok"), "202");
				try {
					client.sendToClient(reply);
					conn.close();
				} catch (IOException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}

			}
		} catch (SQLException ex) {
			System.out.println("SQLException: " + ex.getMessage());
			System.out.println("SQLState: " + ex.getSQLState());
			System.out.println("VendorError: " + ex.getErrorCode());
		}
	}

	/**
	 * Gets the course ID and course name query for the teacher to choose.
	 * Based on the subject id provided by the teacher.
	 * 
	 * @param msg contains the subject id.
	 * @param client the client
	 * @return the course query
	 */
	@SuppressWarnings("unchecked")
	public static void getCourseQuery(Object msg, ConnectionToClient client) {
		Statement stmt;
		Msg reply;
		ArrayList<String> arr = new ArrayList<String>();
		ArrayList<String> courses = new ArrayList<String>();
		arr = (ArrayList<String>)((Msg) msg).getMsg(); 
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
		} catch (Exception ex) {/* handle the error */
		}

		try {
			Connection conn = DriverManager.getConnection(
					"jdbc:mysql://localhost/aes", dblogin, dbpass);
			System.out.println("SQL connection succeed");

			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("select S.CourseID,C.CourseName from subjectcourse S,course C " +
					"where S.CourseID = C.idCourse and S.SubjectId =" + arr.get(0));
			
			if (rs == null) {
				arr.add("error");
				reply = new Msg(arr, "401");
			} else {

				while (rs.next()) {
					courses.add(rs.getString("CourseID"));
					courses.add(rs.getString("CourseName"));
				}
				reply = new Msg(courses, "401");
			}
			try {
				client.sendToClient(reply);
				conn.close();
			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			client.notify();
			rs.close();
		}

		catch (SQLException ex) {
			System.out.println("SQLException: " + ex.getMessage());
			System.out.println("SQLState: " + ex.getSQLState());
			System.out.println("VendorError: " + ex.getErrorCode());
		}
	}

	/**
	 * Run download exam query for the student.
	 * Matched by the exam activation code provided by the student.
	 * Also getting the course name related to the exam.
	 *
	 * @param msg contains the exam activation code.
	 * @param client the client
	 */
	public static void RunDownloadExamQuery(Object msg,	ConnectionToClient client) {
		Statement stmt;
		Statement stmt2 ;

		System.out.println("203");
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
		} catch (Exception ex) {/* handle the error */
		}

		try {
			Connection conn = DriverManager.getConnection(
					"jdbc:mysql://localhost/aes", dblogin, dbpass);
			System.out.println("SQL connection succeed");
			stmt = conn.createStatement();
			stmt2 = conn.createStatement();
			
			ResultSet rs = stmt
					.executeQuery("Select E.ExamId,E.Duration,E.Comments,E.Visible,Q.Question,Q.Description,Q.Answer1,Q.Answer2,Q.Answer3,Q.Answer4,EQ.Points"
							+ " From ActivatedExam AE join Exam E on AE.ExamId=E.ExamId join examquestion EQ on E.ExamId=EQ.ExamId join Questions Q on EQ.Qid=Q.Qid"
							+ " Where AE.ActivationCode="
							+ "\""
							+ ((Msg) msg).getMsg().toString() + "\"");
			
		    rs.first(); //move to first row	
			String examid = (String) rs.getString(1).subSequence(2,4); //get Examid then parse to get Course Id
			rs.beforeFirst(); //move cursor back before first
			
			ResultSet rs2 = stmt2.executeQuery("select  CourseName From Course Where idCourse="+"\""+examid+"\""); //get the course name of exam
		    rs2.first(); //move to first row
		  
		    //get single fields
		    rs.first(); 
		    Exam ManualEx = new Exam();
		    ManualEx.setDuration(Integer.decode(rs.getString(2)) );
		    ManualEx.setExamId(rs.getString(1));
		    ManualEx.setCourseName(rs2.getString(1));		    
		    if (rs.getString(4).equals("1")) ManualEx.setComments(rs.getString(3));
		    else ManualEx.setComments("");
		    rs.beforeFirst();
		    //get single fields
		    
		    ArrayList<Question> Qst = new ArrayList<Question>();
		 
 		    while(rs.next())
		    {	
 		    	Question question1 =new Question();
 		    	ArrayList<String> Answer1 = new ArrayList<String>();    	
 		    	question1.setQuestion(rs.getString(5));
 		    	question1.setDescription(rs.getString(6));
 		    	Answer1.add(rs.getString(7));
 		    	Answer1.add(rs.getString(8));
 		    	Answer1.add(rs.getString(9));
 		    	Answer1.add(rs.getString(10));		    	
 		    	question1.setAnswer(Answer1);
 		    	Qst.add(question1);
 		    	ManualEx.setQuestionPoints(rs.getString(11));
		    }
 		    
 		    ManualEx.setQstManualExam(Qst);
 		    rs.close();
 		    rs2.close();
 		    
 		    for (int i=0;i<stdActExan.size();i++)
 		    {
 		    	
 		    	if (stdActExan.get(i).getClient().equals(client))
 		    			{
 		    				stdActExan.get(i).setExamId(ManualEx.getExamId());
 		    					stdActExan.get(i).setActivationCode(((Msg) msg).getMsg().toString());
 		    			}
 		    }
 			Msg reply = new Msg(ManualEx, "203");
 			
 			try {
 			client.sendToClient(reply);
 			conn.close();
 			}		
 			catch(IOException e){e.printStackTrace();}
		}

		catch (SQLException ex) {
			System.out.println("SQLException: " + ex.getMessage());
			System.out.println("SQLState: " + ex.getSQLState());
			System.out.println("VendorError: " + ex.getErrorCode());
		}
	}

		
	/**
	 * Run download exam automatic query for the students who are 
	 * doing the automatic exam.
	 * Also getting the course name related to the exam.
	 *
	 * @param msg contains the exam activation code.
	 * @param client the client
	 */
	public static void RunDownloadExamAutomaticQuery(Object msg,	ConnectionToClient client) {
		Statement stmt;
		Statement stmt2 ;

		System.out.println("207");
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
		} catch (Exception ex) {/* handle the error */
		}

		try {

			Connection conn = DriverManager.getConnection(
					"jdbc:mysql://localhost/aes", dblogin, dbpass);
			System.out.println("SQL connection succeed");
			stmt = conn.createStatement();
			stmt2 = conn.createStatement();
			
			ResultSet rs = stmt
					.executeQuery("Select E.ExamId,E.Duration,E.Comments,E.Visible,Q.Question,Q.Description,Q.Answer1,Q.Answer2,Q.Answer3,Q.Answer4,EQ.Points,Q.Qid"
							+ " From ActivatedExam AE join Exam E on AE.ExamId=E.ExamId join examquestion EQ on E.ExamId=EQ.ExamId join Questions Q on EQ.Qid=Q.Qid"
							+ " Where AE.ActivationCode="
							+ "\""
							+ ((Msg) msg).getMsg().toString() + "\"");
					
		    rs.first(); //move to first row	
			String examid = (String) rs.getString(1).subSequence(2,4); //get Examid then parse to get Course Id
			rs.beforeFirst(); //move cursor back before thirst
			
			
			ResultSet rs2 = stmt2.executeQuery("select  CourseName From Course Where idCourse="+"\""+examid+"\""); //get the course name of exam
		    rs2.first(); //move to first row
		  
		    //get single fields
		    rs.first(); 
		    Exam ManualEx = new Exam();
		    ManualEx.setDuration(Integer.decode(rs.getString(2)) );
		    ManualEx.setExamId(rs.getString(1));
		    ManualEx.setCourseName(rs2.getString(1));		    
		    if (rs.getString(4).equals("1")) ManualEx.setComments(rs.getString(3));
		    else ManualEx.setComments("");
		    rs.beforeFirst();
		    //get single fields
		    
		    ArrayList<Question> Qst = new ArrayList<Question>();
		 
 		    while(rs.next())
		    {	
 		    	Question question1 =new Question();
 		    	ArrayList<String> Answer1 = new ArrayList<String>();    	
 		    	question1.setQuestion(rs.getString(5));
 		    	question1.setDescription(rs.getString(6));
 		    	Answer1.add(rs.getString(7));
 		    	Answer1.add(rs.getString(8));
 		    	Answer1.add(rs.getString(9));
 		    	Answer1.add(rs.getString(10));		    	
 		    	question1.setAnswer(Answer1);
 		    	question1.setQId(rs.getString(12));
 		    	Qst.add(question1);
 		    	ManualEx.setQuestionPoints(rs.getString(11));
		    }
 		    
 		    ManualEx.setQstManualExam(Qst);
 		    rs.close();
 		    rs2.close();

 		    for (int i=0;i<stdActExan.size();i++)
 		    {
 		    	
 		    	if (stdActExan.get(i).getClient().equals(client))
 		    			{
 		    				stdActExan.get(i).setExamId(ManualEx.getExamId());
 		    					stdActExan.get(i).setActivationCode(((Msg) msg).getMsg().toString());
 		    			}
 		    }
 		    
 			Msg reply = new Msg(ManualEx, "207");
 			
 			try {
 			client.sendToClient(reply);
 			conn.close();
 			}		
 			catch(IOException e){e.printStackTrace();}	
		}

		catch (SQLException ex) {
			System.out.println("SQLException: " + ex.getMessage());
			System.out.println("SQLState: " + ex.getSQLState());
			System.out.println("VendorError: " + ex.getErrorCode());
		}
	}

	
	/**
	 * Gets the questions query for the teacher to see while composing exam.
	 *
	 * @param msg contains the question id.
	 * @param client the client
	 * @return the questions query
	 */
	public static void getQuestionsQuery(Object msg, ConnectionToClient client) {
		Statement stmt;
		Msg reply;
		ArrayList<String> arr = new ArrayList<String>();

		try {Class.forName("com.mysql.jdbc.Driver").newInstance();} 
		catch (Exception ex) {/* handle the error */}
		
		try {
			Connection conn = DriverManager.getConnection(
					"jdbc:mysql://localhost/aes", dblogin, dbpass);
			System.out.println("SQL connection succeed");
			
			stmt = conn.createStatement();
			ResultSet rs = stmt
					.executeQuery("select Question,Description,Answer1,Answer2,Answer3,Answer4 from questions where Qid = "
							+ ((Msg) msg).getMsg().toString());

			if (rs == null || !rs.first()) {
				arr.add("error");
				reply = new Msg(arr, "402");
			}
			else {
				arr.add(rs.getString("Question"));
				arr.add(rs.getString("Description"));
				arr.add(rs.getString("Answer1"));
				arr.add(rs.getString("Answer2"));
				arr.add(rs.getString("Answer3"));
				arr.add(rs.getString("Answer4"));

				reply = new Msg(arr, "402");
			}
			try {
				client.sendToClient(reply);
				conn.close();
			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			client.notify();
			rs.close();
		}

		catch (SQLException ex) {
			System.out.println("SQLException: " + ex.getMessage());
			System.out.println("SQLState: " + ex.getSQLState());
			System.out.println("VendorError: " + ex.getErrorCode());
		}
	}

	/**
	 * Insert exam query. When the teacher finished composing the exam
	 * it stores to the data base.
	 * First there is a verification that this exam id is unique.
	 *
	 * @param msg contains an exam class that is by itslef contains all the
	 * exam information needed for insertion to data base.
	 * 
	 * @param client the client
	 */
	public static void InsertExamQuery(Object msg, ConnectionToClient client) {
		
		Statement stmt;
		Msg reply;
		int fault = 0;
		int sum = 0;
		Exam exam = (Exam)((Msg) msg).getMsg();
		
		try {Class.forName("com.mysql.jdbc.Driver").newInstance();} 
		catch (Exception ex) {/* handle the error */}
		
		try {
			Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/aes", dblogin, dbpass);
			System.out.println("SQL connection succeed");
				
			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("select ExamId from aes.exam where ExamId = " + exam.getExamId());
			
			for(int i=0;i<exam.getQuestionPoints().size();i++){
				sum += Integer.parseInt(exam.getQuestionPoints().get(i));
					if(Integer.parseInt(exam.getQuestionPoints().get(i)) < 0)
						fault = 1;
			}
			for(int i=0;i<exam.getQuestions().size();i++)
				for(int j=i+1;j<exam.getQuestions().size();j++){
					if(exam.getQuestions().get(i).equals(exam.getQuestions().get(j)))
					{
						fault = 1;
						break;
					}	
				}
					
			if (rs.first() || exam.getDuration() <= 0 || exam.getQuestions().isEmpty() || exam.getQuestionPoints().contains("0")
					|| exam.getQuestionPoints().isEmpty() || fault == 1 || sum > 100)
			{
				reply = new Msg("error", "403");
			}
			else{
				
				Calendar currentDate = Calendar.getInstance();
				SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
				String dateNow = formatter.format(currentDate.getTime());
				
				String query = "INSERT INTO `aes`.`exam` (`ExamId`,`TID`,`CreationDate`,`Duration`,`ExamSubject`,`Comments`,`Visible`) VALUES (?,?,?,?,?,?,?)";
				PreparedStatement preparedStmt = (PreparedStatement) conn.prepareStatement(query);
				preparedStmt.setString(1, exam.getExamId());
				preparedStmt.setInt(2, Integer.parseInt(exam.getTeacherId()));
				preparedStmt.setString(3, dateNow);
				preparedStmt.setInt(4, exam.getDuration());
				preparedStmt.setString(5, exam.getExamSubject());
				preparedStmt.setString(6, exam.getDescription());
				preparedStmt.setBoolean(7, exam.isVisible());
				preparedStmt.execute();
				
				String query1 = "INSERT INTO `aes`.`examquestion` (`ExamID`,`Qid`,`Points`) VALUES (?,?,?)";
				PreparedStatement preparedStmt1 = (PreparedStatement) conn.prepareStatement(query1);
				
				for(int i=0;i<exam.getQuestions().size();i++)
				{
					preparedStmt1.setString(1, exam.getExamId());
					preparedStmt1.setString(2, exam.getQuestions().get(i));
					preparedStmt1.setString(3, exam.getQuestionPoints().get(i));
					preparedStmt1.execute();
				}
				
				reply = new Msg("ok", "403");
			}
			
			try {
				client.sendToClient(reply);
			conn.close();} 
			catch (IOException e) {// TODO Auto-generated catch block
				e.printStackTrace();}
			exam.setExamId(null);
			client.notify();
			rs.close();
		}

		catch (SQLException ex) {
			reply = new Msg("error", "403");
			System.out.println("SQLException: " + ex.getMessage());
			System.out.println("SQLState: " + ex.getSQLState());
			System.out.println("VendorError: " + ex.getErrorCode());
		}
	}

	/**
	 * Run get grades exam query.
	 *
	 * @param msg the msg
	 * @param client the client
	 */
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public static void RunGetGradesExamQuery(Object msg,ConnectionToClient client) {
		
		Statement stmt;
		Vector<Vector<String>> resultvec = new Vector();
		ArrayList<String> idcourses = new ArrayList<String>();
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
		} catch (Exception ex) {/* handle the error */
		}

		try {

			Connection conn = DriverManager.getConnection(
					"jdbc:mysql://localhost/aes", dblogin, dbpass);
			System.out.println("SQL connection succeed");

			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(((Msg) msg).toString());
			for(int i=0;rs.next();i++){
				resultvec.add(new Vector<String>());
				for(int j=0;j<3;j++){
					resultvec.get(i).add(rs.getString(j+1));
				}
				idcourses.add(resultvec.get(i).get(0).substring(2, 4));
				System.out.println("Coure id: "+idcourses.get(i));
			}
			
			for(int i=0;i<idcourses.size();i++){
			ResultSet rs2 = stmt.executeQuery("select CourseName from course where idCourse = "+ idcourses.get(i));
			while(rs2.next()){
				resultvec.get(i).add(rs2.getString(1));
	
				}
			}
			Msg reply = new Msg(resultvec,"205");
			
			try {
				client.sendToClient(reply);
			conn.close();} 
			catch (IOException e) {// TODO Auto-generated catch block
				e.printStackTrace();}
			
			client.notify();
			rs.close();
		}
		catch (SQLException ex) {
			System.out.println("SQLException: " + ex.getMessage());
			System.out.println("SQLState: " + ex.getSQLState());
			System.out.println("VendorError: " + ex.getErrorCode());
		}
		
	}

	/**
	 * Gets the active exam query.
	 *
	 * @param msg the msg
	 * @param client the client
	 * @return the active exam query
	 */
	@SuppressWarnings("unchecked")
	public static void getActiveExamQuery(Object msg, ConnectionToClient client) {
		Statement stmt;
		Msg reply = null;
		ArrayList<String> exams = new ArrayList<String>();
		
		try {Class.forName("com.mysql.jdbc.Driver").newInstance();} 
		catch (Exception ex) {/* handle the error */}
		
		try {Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/aes", dblogin, dbpass);
				System.out.println("SQL connection succeed");

			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("select ExamId from aes.exam");
			if (rs == null) {
				exams.add("error");
				reply = new Msg(exams, "404");
				}
			else{
					while(rs.next())
						exams.add(rs.getString("ExamId"));
	
					ResultSet rs1 = stmt.executeQuery("select ExamId from aes.activatedexam");
					if (rs1 == null) {
						exams.add("error");
						reply = new Msg(exams, "404");
						}
					
					else{
						while(rs1.next())
							if(exams.contains(rs1.getString("ExamId")))
								exams.remove(rs1.getString("ExamId"));
						reply = new Msg(exams,"404");
					}
				}
			try {
				client.sendToClient(reply);
			conn.close();} 
			catch (IOException e) {// TODO Auto-generated catch block
				e.printStackTrace();}
			
			client.notify();
			rs.close();	
		}
		catch (SQLException ex) {
			System.out.println("SQLException: " + ex.getMessage());
			System.out.println("SQLState: " + ex.getSQLState());
			System.out.println("VendorError: " + ex.getErrorCode());	
		}
		
	}
	
	/**
	 * Sets the activation code query.
	 *
	 * @param msg the msg
	 * @param client the client
	 */
	public static void setActivationCodeQuery(Object msg,ConnectionToClient client) {
		Statement stmt;
		Msg reply = null;
		@SuppressWarnings("unchecked")
		ArrayList<String> active = (ArrayList<String>) ((Msg) msg).getMsg();
		
		try {Class.forName("com.mysql.jdbc.Driver").newInstance();} 
		catch (Exception ex) {/* handle the error */}
		
		try {Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/aes", dblogin, dbpass);
				System.out.println("SQL connection succeed");
			

			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("select ExamId from aes.activatedexam where ActivationCode = " + active.get(1));
			
			if(rs.first() == true)
			{
				active.clear();
				System.out.printf("\nExam with the same activation code already exists\n");
				active.add("error");
				reply = new Msg(active, "405");
				
			}
			else
			{	
			System.out.printf("\nSetting New Code\n");
			String query = "INSERT INTO `aes`.`activatedexam` (`ExamId`,`ActivationCode`) VALUES (?,?)";
			PreparedStatement Stmt = (PreparedStatement) conn.prepareStatement(query);
			
		
				Stmt.setString(1, active.get(0));
				Stmt.setString(2, active.get(1));
				Stmt.execute();
				reply = new Msg(true, "405");
					
			try {
				client.sendToClient(reply);
				conn.close();} 
			catch (IOException e) {// TODO Auto-generated catch block
				e.printStackTrace();}
			
			client.notify();
			}
		}
		catch (SQLException ex) {
			System.out.println("SQLException: " + ex.getMessage());
			System.out.println("SQLState: " + ex.getSQLState());
			System.out.println("VendorError: " + ex.getErrorCode());	
		}
		
		
	}

	/**
	 * Gets the stat exam query.
	 *
	 * @param msg the msg
	 * @param client the client
	 * @return the stat exam query
	 */
	@SuppressWarnings("unchecked")
	public static void getStatExamQuery(Object msg, ConnectionToClient client) {
		Statement stmt;
		Msg reply = null;
		String Query = (String) ((Msg) msg).getMsg();
		ArrayList<String> ExamId = new ArrayList<String>();
		
		try {Class.forName("com.mysql.jdbc.Driver").newInstance();} 
		catch (Exception ex) {/* handle the error */}
		
		try {Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/aes", dblogin, dbpass);
				System.out.println("SQL connection succeed");

				stmt = conn.createStatement();
				ResultSet rs = stmt.executeQuery(Query);
				while(rs.next())
				{
					ExamId.add(rs.getString("ExamId"));
				}
				
				
				reply = new Msg(ExamId,"406");
			
				
			try {
				client.sendToClient(reply);
				conn.close();} 
			catch (IOException e) {// TODO Auto-generated catch block
				e.printStackTrace();}
			
			client.notify();	
		}
		catch (SQLException ex) {
			System.out.println("SQLException: " + ex.getMessage());
			System.out.println("SQLState: " + ex.getSQLState());
			System.out.println("VendorError: " + ex.getErrorCode());	
		}
		
		
	}


		
	/**
	 * Run save manual exam file.
	 *
	 * @param msg the msg
	 * @param client the client
	 */
	public static void RunSaveManualExamFile(Object msg, ConnectionToClient client)
	{
			System.out.println("204 ");
			FileSend examToSave = (FileSend)((Msg) msg).getMsg();
			
			try {Class.forName("com.mysql.jdbc.Driver").newInstance();} 
			
			catch (Exception ex) {/* handle the error */}
			
			try {
				Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/aes", dblogin, dbpass);
				System.out.println("SQL connection succeed");				
				String query = "INSERT INTO `aes`.`studentexammanual` (`ExamId`,`Sid`) VALUES (?,?)";
				PreparedStatement Stmt = (PreparedStatement) conn.prepareStatement(query);
					Stmt.setString(1, examToSave.getExamID());
					Stmt.setInt(2,Integer.valueOf(examToSave.getStudentId()));  
					Stmt.execute();
					conn.close();		
			}
			catch (SQLException ex) {
				System.out.println("SQLException: " + ex.getMessage());
				System.out.println("SQLState: " + ex.getSQLState());
				System.out.println("VendorError: " + ex.getErrorCode());	
			}
			
			//////Saving file part/////
			
			File dir = new File("exams");
			dir.mkdir();
			
			      try {
			    	FileOutputStream out = new FileOutputStream("exams\\"+examToSave.getExamID()+"_"+examToSave.getStudentId()+".doc");  		 
					out.write(examToSave.getMybytearray(),0,examToSave.getSize() );
				    out.close();   
				} catch (IOException e) {
					
					e.printStackTrace();
				}

			
	}

	/**
	 * Run delete active code query.
	 *
	 * @param msg the msg
	 * @param client the client
	 */
	public static void RunDeleteActiveCodeQuery(Object msg,	ConnectionToClient client) {
		System.out.println("206 ");
		String active = (String) ((Msg) msg).getMsg();
		
		try {Class.forName("com.mysql.jdbc.Driver").newInstance();} 
		
		catch (Exception ex) {/* handle the error */}
		
		
		try {
			Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/aes", dblogin, dbpass);
			System.out.println("SQL connection succeed");		
			Statement stmt = conn.createStatement();
			int succes = stmt.executeUpdate("DELETE FROM `aes`.`activatedexam` WHERE `ExamId`="+"\""+active+"\"");
			
			//	client.sendToClient(reply);
			conn.close();		
		}
		catch (SQLException ex) {
			System.out.println("SQLException: " + ex.getMessage());
			System.out.println("SQLState: " + ex.getSQLState());
			System.out.println("VendorError: " + ex.getErrorCode());	
		}
	}

	/**
	 * Run get requests query.
	 *
	 * @param msg the msg
	 * @param client the client
	 */
	@SuppressWarnings("unchecked")
	public static void RunGetRequestsQuery(Object msg, ConnectionToClient client) {
		Statement stmt;
		Msg reply = null;
		ArrayList<String> Req = new ArrayList<String>();
		
		
		try {Class.forName("com.mysql.jdbc.Driver").newInstance();} 
		catch (Exception ex) {/* handle the error */}
		
		try {Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/aes", dblogin, dbpass);
				System.out.println("SQL connection succeed");

				stmt = conn.createStatement();
				ResultSet rs1 = stmt.executeQuery("select RequestId, TID, ExamId, RequestTime, Request from request");
				if (rs1 == null) {
					Req.add("error");
					reply = new Msg(Req, "300");
					}
				else
				{
					while(rs1.next())
					{
						Req.add(rs1.getString("RequestId"));
						Req.add(rs1.getString("TID"));
						Req.add(rs1.getString("ExamId"));
						Req.add(rs1.getString("RequestTime"));
						Req.add(rs1.getString("Request"));
						
					}
					reply = new Msg(Req,"300");
				}
			try {
				client.sendToClient(reply);
				conn.close();} 
			catch (IOException e) {// TODO Auto-generated catch block
				e.printStackTrace();
				}
			
			client.notify();	
		}
		catch (SQLException ex) {
			System.out.println("SQLException: " + ex.getMessage());
			System.out.println("SQLState: " + ex.getSQLState());
			System.out.println("VendorError: " + ex.getErrorCode());	
		}
		
	}

	/**
	 * Run get active exams query.
	 *
	 * @param msg the msg
	 * @param client the client
	 */
	@SuppressWarnings("unchecked")
	public static void runGetActiveExamsQuery(Object msg,ConnectionToClient client) {
		Statement stmt;
		Msg reply = null;
		ArrayList<String> activated = new ArrayList<String>();
		activated = (ArrayList<String>) ((Msg) msg).getMsg();
		
		try {Class.forName("com.mysql.jdbc.Driver").newInstance();} 
		catch (Exception ex) {/* handle the error */}
		
		try {Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/aes", dblogin, dbpass);
				System.out.println("SQL connection succeed");

				stmt = conn.createStatement();
				ResultSet rs1 = stmt.executeQuery("select ExamId from aes.activatedexam");
				
				if (rs1 == null) {
					activated.add("error");
					reply = new Msg(activated, "408");
					}
				
				while(rs1.next())
					activated.add(rs1.getString("ExamId"));
						
				reply = new Msg(activated,"408");
			try {
				client.sendToClient(reply);
				conn.close();} 
			catch (IOException e) {// TODO Auto-generated catch block
				e.printStackTrace();
				}
			
			client.notify();	
		}
		catch (SQLException ex) {
			System.out.println("SQLException: " + ex.getMessage());
			System.out.println("SQLState: " + ex.getSQLState());
			System.out.println("VendorError: " + ex.getErrorCode());	
		}
		
	}

	/**
	 * Run completed automatic exam query.
	 *
	 * @param msg the msg
	 * @param client the client
	 */
	public static void RunCompletedAutomaticExamQuery(Object msg,ConnectionToClient client) {		
		Exam exam1 = (Exam) ((Msg) msg).getMsg();
		
		try {Class.forName("com.mysql.jdbc.Driver").newInstance();} 
		catch (Exception ex) {/* handle the error */}
		try 
		{
		Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/aes", dblogin, dbpass);
		System.out.println("SQL connection succeed");
		
		String query = "INSERT INTO `aes`.`studentexam` (`Sid`,`ExamId`,`Qid`,`Ans`) VALUES (?,?,?,?)";
		PreparedStatement preparedStmt = (PreparedStatement) conn.prepareStatement(query);
		
		for (int i=0;i<exam1.getQstManualExam().size();i++)
			{
				preparedStmt.setInt(1, Integer.parseInt(exam1.getStudentId() ) );
				preparedStmt.setString(2, exam1.getExamId() );
				preparedStmt.setString(3, exam1.getQstManualExam().get(i).getQId() );
				preparedStmt.setString(4,String.valueOf(exam1.getQstManualExam().get(i).getSelectedAnsExAuto() ) );
				preparedStmt.execute();
			}
		////////////////Check Exam /////////////////////////////////////////
		Statement stmt2 = conn.createStatement();
		String update1 = "update studentexam Se,questions Q Set Se.IsCorrect = 'y' where Se.Qid = Q.QId and Se.Ans = Q.CorrectAns and Se.Sid = " + exam1.getStudentId() + " and Se.ExamId = " + exam1.getExamId();
		String update2 = "update studentexam Se,questions Q Set Se.IsCorrect = 'n' where Se.Qid = Q.QId and Se.Ans <> Q.CorrectAns and Se.Sid = " + exam1.getStudentId() + " and Se.ExamId = " + exam1.getExamId();
		stmt2.executeUpdate(update1);
		stmt2.executeUpdate(update2);
	
		String CheckExamQuery = "Select sum(EQ.points) As Grade from examquestion Eq,studentexam Se,questions Q where Eq.Qid = Se.Qid and Se.Qid = Q.Qid and Q.CorrectAns = Se.Ans and Eq.ExamId = Se.ExamId and Se.Sid = "+ exam1.getStudentId() +" and Se.ExamId = " + exam1.getExamId();
		Statement stmt = conn.createStatement();
		ResultSet rs = stmt.executeQuery(CheckExamQuery);
		Calendar currentDate = Calendar.getInstance();
		SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
		String dateNow = formatter.format(currentDate.getTime());
		if (rs == null || !rs.first()) 
		{
	
			System.out.println("Error Check Exam");
		}
		else{
			String query2 = "INSERT INTO `aes`.`finishedexam` (`Sid`,`ExamId`,`StdExamDuration`,`Grade`,`Date`,`FinishedInTime`,`Approved`) VALUES (?,?,?,?,?,?,?)";
		    PreparedStatement preparedStmt2 = (PreparedStatement) conn.prepareStatement(query2);
		    preparedStmt2.setInt(1, Integer.parseInt(exam1.getStudentId() ) );
			preparedStmt2.setString(2, exam1.getExamId() );
			preparedStmt2.setString(3,exam1.getActualStudentExamDuration());
			if(rs.getString(1) != null)
				preparedStmt2.setString(4, rs.getString(1));
			else
				preparedStmt2.setString(4, "0");
			preparedStmt2.setString(5,dateNow);
			preparedStmt2.setString(6,exam1.getFinishedintime());
			preparedStmt2.setString(7,"n");
			preparedStmt2.execute();
			
		}
		conn.close();
		
	    for (int i=0;i<stdActExan.size();i++)
		    {
		    	
		    	if (stdActExan.get(i).getClient().equals(client))
		    			{
		    					stdActExan.get(i).setExamId(null);
		    					stdActExan.get(i).setActivationCode(null);
		    			}
		    }
		
		
		}
		
		catch (SQLException ex) {
			System.out.println("SQLException: " + ex.getMessage());
			System.out.println("SQLState: " + ex.getSQLState());
			System.out.println("VendorError: " + ex.getErrorCode());	
		}
	}


	/**
	 * Run set request query.
	 *
	 * @param msg the msg
	 * @param client the client
	 */
	@SuppressWarnings("unchecked")
	public static void runSetRequestQuery(Object msg, ConnectionToClient client) {
		
		Statement stmt;
		Msg reply;
		ArrayList<String> request = (ArrayList<String>)((Msg) msg).getMsg();
		
		try {Class.forName("com.mysql.jdbc.Driver").newInstance();} 
		catch (Exception ex) {/* handle the error */}
		
		try {
			Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/aes", dblogin, dbpass);
			System.out.println("SQL connection succeed");

			
				String query = "INSERT INTO `aes`.`request` (`TID`,`ExamID`,`RequestTime`,`Request`) VALUES (?,?,?,?)";
				PreparedStatement preparedStmt = (PreparedStatement) conn.prepareStatement(query);
				
				preparedStmt.setString(1, request.get(0));
				preparedStmt.setString(2, request.get(1));
				preparedStmt.setString(3, request.get(2));
				preparedStmt.setString(4, request.get(3));
				preparedStmt.execute();
				reply = new Msg("ok", "409");
			try {
				client.sendToClient(reply);
			conn.close();} 
			catch (IOException e) {// TODO Auto-generated catch block
				e.printStackTrace();}
			
			client.notify();
		}

		catch (SQLException ex) {
			System.out.println("SQLException: " + ex.getMessage());
			System.out.println("SQLState: " + ex.getSQLState());
			System.out.println("VendorError: " + ex.getErrorCode());	
		}
		
	}




	/**
	 * Run admin fill teacher combo box query.
	 *
	 * @param msg the msg
	 * @param client the client
	 */
	public static void RunAdminFillTeacherComboBoxQuery(Object msg,
			ConnectionToClient client) {
	
		Statement stmt;
		ArrayList<UserEntity> teachers = new ArrayList<UserEntity>();
		try {Class.forName("com.mysql.jdbc.Driver").newInstance();}
		catch (Exception ex) {/* handle the error */}
		
		try {

			Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/aes", dblogin, dbpass);
			System.out.println("SQL connection succeed");
			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(((Msg) msg).toString());
			
			while(rs.next())
			{		
					UserEntity t1 = new UserEntity(	String.valueOf(rs.getInt(1)), rs.getString(2),  rs.getString(3), "", "", "");
					teachers.add(t1);
			}
			
			Msg reply = new Msg(teachers, "302");
			
			
				try {
					client.sendToClient(reply);
				} catch (IOException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				
			conn.close(); 
			
		}
		
		catch (SQLException ex) {
			System.out.println("SQLException: " + ex.getMessage());
			System.out.println("SQLState: " + ex.getSQLState());
			System.out.println("VendorError: " + ex.getErrorCode());	
		}
		
	}
	
		
		
	/**
 * Run get qid query.
 *
 * @param msg the msg
 * @param client the client
 */
@SuppressWarnings("unchecked")
	public static void runGetQidQuery(Object msg, ConnectionToClient client) {
		Statement stmt;
		Msg reply;
		ArrayList<String> arr = new ArrayList<String>();
		ArrayList<String> Qid = new ArrayList<String>();
		arr = (ArrayList<String>)((Msg) msg).getMsg(); 
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
		} catch (Exception ex) {/* handle the error */
		}

		try {
			Connection conn = DriverManager.getConnection(
					"jdbc:mysql://localhost/aes", dblogin, dbpass);
			System.out.println("SQL connection succeed");

			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("select QId from questions where Subject = " + arr.get(0));
			
			if (rs == null) {
				arr.add("error");
				reply = new Msg(arr, "413");
			} else {

				while (rs.next()) 
					Qid.add(rs.getString("QId"));

				reply = new Msg(Qid, "413");
			}
			try {
				client.sendToClient(reply);
				conn.close();
			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			client.notify();
			rs.close();
		}

		catch (SQLException ex) {
			System.out.println("SQLException: " + ex.getMessage());
			System.out.println("SQLState: " + ex.getSQLState());
			System.out.println("VendorError: " + ex.getErrorCode());
		}
		
	}


	//do query to get all examId of the teacher//
	/**
	 * Run admin get exam id query.
	 *
	 * @param msg the msg
	 * @param client the client
	 */
	public static void RunAdminGetExamIdQuery(Object msg,ConnectionToClient client) 
	{
		System.out.println("ok3 Server Logic run RunAdminGetExamIdQuery");
		Statement stmt;
		String teacherId = (String) ((Msg) msg).getMsg();
		try {Class.forName("com.mysql.jdbc.Driver").newInstance();}
		catch (Exception ex) {/* handle the error */}
				
		try {
			 Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/aes", dblogin, dbpass);
		    System.out.println("SQL connection succeed");
		    stmt = conn.createStatement();
		    ResultSet rs = stmt.executeQuery("Select E.ExamId from exam E Where E.TID ="+"\""+teacherId+"\"");
		    ArrayList<String> ExamId=new ArrayList<String>() ;///aray of examId to fill "comboBoxExamId"
		      while (rs.next())		    	
			           ExamId.add(rs.getString(1));	
		    Msg	 reply = new Msg(ExamId,"311");
			try {
				client.sendToClient(reply);
				conn.close();} 
			catch (IOException e) {// TODO Auto-generated catch block
				e.printStackTrace();
				}
			
			client.notify();	
			}
			catch (SQLException ex) {
				System.out.println("SQLException: " + ex.getMessage());
				System.out.println("SQLState: " + ex.getSQLState());
				System.out.println("VendorError: " + ex.getErrorCode());	
				}
		}
		    
		    
	
	

	
	/**
	 * Run get not approved exam query.
	 *
	 * @param msg the msg
	 * @param client the client
	 */
	@SuppressWarnings("unchecked")
	public static void runGetNotApprovedExamQuery(Object msg,ConnectionToClient client) {
		Statement stmt;
		Msg reply = null;
		String TId = (String) ((Msg) msg).getMsg();
		ArrayList<String> Info = new ArrayList<String>();
		
		try {Class.forName("com.mysql.jdbc.Driver").newInstance();} 
		catch (Exception ex) {/* handle the error */}
		
		try {Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/aes", dblogin, dbpass);
				System.out.println("SQL connection succeed");
				
				stmt = conn.createStatement();
				ResultSet rs = stmt.executeQuery("Select Distinct E.ExamId from exam E, finishedexam Fe where E.ExamId = Fe.ExamId and Fe.Approved = 'n' and E.TId = " + TId);
				while(rs.next())
				{
					Info.add(rs.getString(1));
				}
				
				if (Info.isEmpty()) {
					Info.add("error");

					reply = new Msg(Info, "410");
					}
				else
				{
				
					reply = new Msg(Info,"410");	
				}
				
			try {
				client.sendToClient(reply);
				conn.close();} 
			catch (IOException e) {// TODO Auto-generated catch block
				e.printStackTrace();
				}
			
			client.notify();	
		}
		catch (SQLException ex) {
			System.out.println("SQLException: " + ex.getMessage());
			System.out.println("SQLState: " + ex.getSQLState());
			System.out.println("VendorError: " + ex.getErrorCode());	
		}
		
		
	}

	/**
	 * Run get not approved info query.
	 *
	 * @param msg the msg
	 * @param client the client
	 */
	public static void runGetNotApprovedInfoQuery(Object msg,
			ConnectionToClient client) {
		Statement stmt;
		int i;
		String ExamId = (String) ((Msg) msg).getMsg();
		Vector<Vector<String>> resultvec = new Vector<Vector<String>>();
		Msg reply;
		try {Class.forName("com.mysql.jdbc.Driver").newInstance();} 
		catch (Exception ex) {/* handle the error */}
		
		try {Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/aes", dblogin, dbpass);
				System.out.println("SQL connection succeed");
				
				stmt = conn.createStatement();
				ResultSet rs = stmt.executeQuery("Select Fe.Sid, Fe.Date, Fe.Grade from finishedexam Fe where Fe.Approved = 'n' and Fe.ExamId = " +ExamId );
				for( i=0;rs.next();i++){
					resultvec.add(new Vector<String>());
					for(int j=0;j<3;j++){
						resultvec.get(i).add(rs.getString(j+1));
					}
				}
	
				if (i == 0) {
					resultvec.add(new Vector<String>());
					resultvec.get(0).add("error");
					reply = new Msg(resultvec, "411");
					}
				else
				{
					reply = new Msg(resultvec,"411");	
				}
				
			
			try {
				client.sendToClient(reply);
				conn.close();} 
			catch (IOException e) {// TODO Auto-generated catch block
				e.printStackTrace();
				}
			
			client.notify();	
		}
		catch (SQLException ex) {
			System.out.println("SQLException: " + ex.getMessage());
			System.out.println("SQLState: " + ex.getSQLState());
			System.out.println("VendorError: " + ex.getErrorCode());	
		}
		
	}

	/**
	 * Run admin get exams query.
	 *
	 * @param msg the msg
	 * @param client the client
	 */
	public static void RunAdminGetExamsQuery(Object msg,ConnectionToClient client) {
		Statement stmt;
		Msg reply;
		ArrayList<String> exams = new ArrayList<String>();
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
		} catch (Exception ex) {/* handle the error */
		}

		try {

			Connection conn = DriverManager.getConnection(
					"jdbc:mysql://localhost/aes", dblogin, dbpass);
			System.out.println("SQL connection succeed");

			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(((Msg) msg).toString());
			
			if (rs == null) {
				exams.add("error");
				reply = new Msg(exams,"304");
			} 
			else 
		
			{
				while(rs.next())
				{
					exams.add(rs.getString("ExamId"));
					exams.add(rs.getString("TID"));
					exams.add(rs.getString("CreationDate"));
					exams.add(rs.getString("ExamSubject"));
				}	
					
				reply = new Msg(exams,"304");
			}
			try {
				client.sendToClient(reply);
			conn.close();} 
			catch (IOException e) {// TODO Auto-generated catch block
				e.printStackTrace();}
			
			client.notify();
			rs.close();
		}
		catch (SQLException ex) {
			System.out.println("SQLException: " + ex.getMessage());
			System.out.println("SQLState: " + ex.getSQLState());
			System.out.println("VendorError: " + ex.getErrorCode());
		}
		
	}

	/**
	 * Run insert approve qid query.
	 *
	 * @param msg the msg
	 * @param client the client
	 */
	public static void runInsertApproveQidQuery(Object msg,
			ConnectionToClient client) {
		// TODO Auto-generated method stub
		ArrayList<String> info = (ArrayList<String>) ((Msg) msg).getMsg();
		
		try {Class.forName("com.mysql.jdbc.Driver").newInstance();} 
		catch (Exception ex) {/* handle the error */}
	
		try {Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/aes", dblogin, dbpass);
				System.out.println("SQL connection succeed");
				Statement stmt = conn.createStatement();
				for(int i=1; i< info.size();i++ ){	
				String update = "update finishedexam FE set Fe.Approved = 'y' where Fe.Sid = "+ Integer.parseInt(info.get(i))  + " and Fe.ExamId = " +info.get(0);
				stmt.executeUpdate(update);
				}
			
		}
		catch (SQLException ex) {
			System.out.println("SQLException: " + ex.getMessage());
			System.out.println("SQLState: " + ex.getSQLState());
			System.out.println("VendorError: " + ex.getErrorCode());	
		}
		
	}

	/**
	 * Run admin get questions query.
	 *
	 * @param msg the msg
	 * @param client the client
	 */
	public static void RunAdminGetQuestionsQuery(Object msg,ConnectionToClient client) {
		Statement stmt;
		Msg reply;
		ArrayList<String> quest = new ArrayList<String>();
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
		} catch (Exception ex) {/* handle the error */
		}

		try {
			Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/aes", dblogin, dbpass);
			System.out.println("SQL connection succeed");

			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(((Msg) msg).toString());
			
			if (rs == null) {
				quest.add("error");
				reply = new Msg(quest,"305");
			} 
			else 
		
			{
				while(rs.next())
				{
					quest.add(rs.getString("QId"));
					quest.add(rs.getString("TId"));
					quest.add(rs.getString("Subject"));
					quest.add(rs.getString("Question"));
				}	
					
				reply = new Msg(quest,"305");
			}
			try {
				client.sendToClient(reply);
			conn.close();} 
			catch (IOException e) {// TODO Auto-generated catch block
				e.printStackTrace();}
			
			client.notify();
			rs.close();
		}
		catch (SQLException ex) {
			System.out.println("SQLException: " + ex.getMessage());
			System.out.println("SQLState: " + ex.getSQLState());
			System.out.println("VendorError: " + ex.getErrorCode());
		}
		
	}
	
	/**
	 * Run add exam time function.
	 *
	 * @param msg the msg
	 * @param client the client
	 */
	public static void RunAddExamTimeFunction(Object msg,ConnectionToClient client) {
		
		Statement stmt;
		ArrayList<String> Req = new ArrayList<String>();
	
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
		} catch (Exception ex) {/* handle the error */
		}
		
	
		 Req=(ArrayList<String>) ((Msg) msg).getMsg();
		/*
		makom 0 - request id
		makom 1  - examid   //if null only delete request 
		makom 2  time
		*/
		System.out.println("RunAddExamTimeFunction:");	


		if (Req.size()==2) 
		{	
		
			try {
				Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/aes", dblogin, dbpass);
				System.out.println("SQL connection succeed ,Only Delete");		
				stmt = conn.createStatement();
				int succes = stmt.executeUpdate("DELETE FROM `aes`.`request` WHERE `RequestId`="+"\""+Req.get(0)+"\""); //delete request
				conn.close();		
			}
			catch (SQLException ex) {
				System.out.println("SQLException: " + ex.getMessage());
				System.out.println("SQLState: " + ex.getSQLState());
				System.out.println("VendorError: " + ex.getErrorCode());	
			}
			return;
		}
		
		
		else
		{
			for (int i=0;i<stdActExan.size();i++)
		    {
		    	
				System.out.print(stdActExan.get(i).getExamId());
				
		    	if (Req.get(1).equals(stdActExan.get(i).getExamId()) )
		    			{
		    					System.out.print("Sending");
		    					Msg reply = new Msg(Req.get(2),"306");
		    					try {
									stdActExan.get(i).getClient().sendToClient(reply);
								} catch (IOException e) {
									
									e.printStackTrace();
								}		
		    			}
		    }
		
			try {
				Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/aes", dblogin, dbpass);
				System.out.println("SQL connection succeed ,Send to all and Delete");		
				stmt = conn.createStatement();
				int succes = stmt.executeUpdate("DELETE FROM `aes`.`request` WHERE `RequestId`="+"\""+Req.get(0)+"\""); //delete request
				conn.close();		
			}
			catch (SQLException ex) {
				System.out.println("SQLException: " + ex.getMessage());
				System.out.println("SQLState: " + ex.getSQLState());
				System.out.println("VendorError: " + ex.getErrorCode());	
			}	
			
			
		}
	}

	/**
	 * Run lock exam query.
	 *
	 * @param msg the msg
	 * @param client the client
	 */
	public static void runLockExamQuery(Object msg, ConnectionToClient client) {
		String examtolock = new String();
		examtolock= (String) ((Msg) msg).getMsg();
		System.out.println("runLockExamQuery:");	
		System.out.println(examtolock);	
	
		String lock = new String("lock");
	
		for (int i=0;i<stdActExan.size();i++)
	    {
	    	
			System.out.print(stdActExan.get(i).getExamId());
			
	    	if (examtolock.equals(stdActExan.get(i).getExamId()) )
	    			{
	    					System.out.print("Sending");
	    					stdActExan.get(i).setExamId(null);
	    					stdActExan.get(i).setActivationCode(null);
	    					Msg reply = new Msg(lock ,"416");
	    					try {
								stdActExan.get(i).getClient().sendToClient(reply);
							} catch (IOException e) {
								
								e.printStackTrace();
							}		
	    			}
	    }
		
	
	}

	/**
	 * Run change grade query.
	 *
	 * @param msg the msg
	 * @param client the client
	 */
	public static void runChangeGradeQuery(Object msg, ConnectionToClient client) {
		// TODO Auto-generated method stub
		ArrayList<String> info = (ArrayList<String>) ((Msg) msg).getMsg();
		System.out.println(info);
		try {Class.forName("com.mysql.jdbc.Driver").newInstance();} 
		catch (Exception ex) {/* handle the error */}
	
		try {Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/aes", dblogin, dbpass);
				System.out.println("SQL connection succeed");
				Statement stmt = conn.createStatement();
				for(int i=1; i< info.size();i++ ){	
				String update = "update finishedexam FE set Fe.Grade = " +info.get(3) +" where Fe.Sid = "+ Integer.parseInt(info.get(1))  + " and Fe.ExamId = " +info.get(0);
				String update2 = "update finishedexam FE set Fe.changeDescription = '" + info.get(4) +"' where Fe.Sid = "+ Integer.parseInt(info.get(1))  + " and Fe.ExamId = " +info.get(0);
				String update3 = "update finishedexam FE set Fe.Approved = 'y' where Fe.Sid = "+ Integer.parseInt(info.get(1))  + " and Fe.ExamId = " +info.get(0);
				stmt.executeUpdate(update);
				stmt.executeUpdate(update2);
				stmt.executeUpdate(update3);
				}
			
		}
		catch (SQLException ex) {
			System.out.println("SQLException: " + ex.getMessage());
			System.out.println("SQLState: " + ex.getSQLState());
			System.out.println("VendorError: " + ex.getErrorCode());	
		}
		
	}

	/**
	 * Runget courses query.
	 *
	 * @param msg the msg
	 * @param client the client
	 */
	@SuppressWarnings("unchecked")
	public static void RungetCoursesQuery(Object msg, ConnectionToClient client) {
		Statement stmt;
		Msg reply = null;
		ArrayList<String> courseID = new ArrayList<String>();
		courseID = (ArrayList<String>) ((Msg) msg).getMsg();
		
		try {Class.forName("com.mysql.jdbc.Driver").newInstance();} 
		catch (Exception ex) {/* handle the error */}
		
		try {Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/aes", dblogin, dbpass);
				System.out.println("SQL connection succeed");

				stmt = conn.createStatement();
				ResultSet rs1 = stmt.executeQuery("select idCourse,CourseName from course");
				
				if (rs1 == null) {
					courseID.add("error");
					reply = new Msg(courseID, "307");
					}
				
				while(rs1.next()){
					courseID.add(rs1.getString("idCourse"));
					courseID.add(rs1.getString("CourseName"));
				}
					
						
				reply = new Msg(courseID,"307");
			try {
				client.sendToClient(reply);
				conn.close();} 
			catch (IOException e) {// TODO Auto-generated catch block
				e.printStackTrace();
				}
			
			client.notify();	
		}
		catch (SQLException ex) {
			System.out.println("SQLException: " + ex.getMessage());
			System.out.println("SQLState: " + ex.getSQLState());
			System.out.println("VendorError: " + ex.getErrorCode());	
		}
		
		
	}
	
	
		/**
		 * Run get exam report.
		 *
		 * @param msg the msg
		 * @param client the client
		 */
		public static void RunGetExamReport(Object msg, ConnectionToClient client)
		{
			ArrayList<Integer> grades = new ArrayList<Integer>();
			Msg reply;
			
			try {Class.forName("com.mysql.jdbc.Driver").newInstance();} 
			
			catch (Exception ex) {/* handle the error */}
			
			try {
				Connection conn = DriverManager.getConnection(
						"jdbc:mysql://localhost/aes", dblogin, dbpass);
				System.out.println("SQL connection succeed");
				
				Statement stmt = conn.createStatement();
				ResultSet rs = stmt	.executeQuery("Select Fe.Grade from finishedexam Fe where Fe.ExamId = "+ (String) ((Msg) msg).getMsg());

				while(rs.next())
					grades.add(Integer.parseInt(rs.getString("Grade")));
	
				reply = new Msg(grades,"421");
				
				try {
					client.sendToClient(reply);
					conn.close();
				} catch (IOException e) {
					
					e.printStackTrace();
				}
				
				rs.close();
			}

			catch (SQLException ex) {
				System.out.println("SQLException: " + ex.getMessage());
				System.out.println("SQLState: " + ex.getSQLState());
				System.out.println("VendorError: " + ex.getErrorCode());
			}
	    }



		/**
		 * Runget fininsed exam info.
		 *
		 * @param msg the msg
		 * @param client the client
		 */
		public static void RungetFininsedExamInfo(Object msg,
				ConnectionToClient client) {
			// TODO Auto-generated method stub
			Statement stmt;
			ResultSet rs;
			Vector<Vector<String>> info = new Vector<Vector<String>>();
			ArrayList<String> examInfo = (ArrayList<String>) ((Msg) msg).getMsg();
			
			try {Class.forName("com.mysql.jdbc.Driver").newInstance();} 
			catch (Exception ex) {/* handle the error */}
		
			try {Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/aes", dblogin, dbpass);
					System.out.println("SQL connection succeed");
					
					stmt = conn.createStatement();
					rs = stmt.executeQuery("select Q.QId,Q.Question,Q.Answer1,Q.Answer2,Q.Answer3,Q.Answer4,Q.CorrectAns, Se.Ans " + 
											"from questions Q, studentexam Se ,finishedexam Fe " +
											"where Q.QId = Se.Qid And Se.ExamId = '"+ examInfo.get(1) + "' and Q.QId IN " +
											"(Select Se.Qid from studentexam Se where Se.SId = '"+ Integer.parseInt(examInfo.get(0))+"' and Se.ExamId = '"+ examInfo.get(1) + "') group by Q.QId" );
					
					for(int i=0;rs.next();i++){
						info.add(new Vector<String>());
						for(int j=0;j<8;j++){
							info.get(i).add(rs.getString(j+1));
						}
						
					}
					Msg reply = new Msg(info,"210");
					try {
						client.sendToClient(reply);
					    conn.close();} 
					catch (IOException e) {// TODO Auto-generated catch block
						e.printStackTrace();}				
					client.notify();
					rs.close();
				
			}
			catch (SQLException ex) {
				System.out.println("SQLException: " + ex.getMessage());
				System.out.println("SQLState: " + ex.getSQLState());
				System.out.println("VendorError: " + ex.getErrorCode());	
			}
		}


		/**
		 * Run get students grades query.
		 *
		 * @param msg the msg
		 * @param client the client
		 */
		public static void runGetStudentsGradesQuery(Object msg,ConnectionToClient client) {
			
			
		}

		/**
		 * Run getmanual examslist query.
		 *
		 * @param msg the msg
		 * @param client the client
		 */
		public static void RunGetmanualExamslistQuery(Object msg,
				ConnectionToClient client) {
			System.out.println("209 ");
			String useid = (String) ((Msg) msg).getMsg();
			ArrayList<String> arr = new ArrayList<String>();
			Msg reply=null;
			try {Class.forName("com.mysql.jdbc.Driver").newInstance();} 
			
			catch (Exception ex) {/* handle the error */}
			
			try {
				Connection conn = DriverManager.getConnection(
						"jdbc:mysql://localhost/aes", dblogin, dbpass);
				System.out.println("SQL connection succeed");
				
				Statement stmt = conn.createStatement();
				ResultSet rs = stmt	.executeQuery("SELECT ExamId FROM studentexammanual where Sid="+"\""+useid+"\"");

			
				if (rs == null || !rs.first()) {
					arr.add("error");
					 reply = new Msg(arr, "209");
												}
				else {
					rs.beforeFirst();
					while(rs.next())
						{
						arr.add(rs.getString(1));
						}
					
					reply = new Msg(arr, "209");
				}
				try {
					client.sendToClient(reply);
					conn.close();
				} catch (IOException e) {
					
					e.printStackTrace();
				}
				
				rs.close();
			}

			catch (SQLException ex) {
				System.out.println("SQLException: " + ex.getMessage());
				System.out.println("SQLState: " + ex.getSQLState());
				System.out.println("VendorError: " + ex.getErrorCode());
			}

			
			
			
		}

		/**
		 * Rundownoad manual exam copyt query.
		 *
		 * @param msg the msg
		 * @param client the client
		 */
		public static void RundownoadManualExamCopytQuery(Object msg,
				ConnectionToClient client) {
		
			ArrayList<String> info = (ArrayList<String>)((Msg) msg).getMsg();
			
			FileSend  outf= new FileSend("exam");
			
			
			  String LocalfilePath=new String("cheked\\" +info.get(0) + "_" + info.get(1)+ ".doc" );
			//  String LocalfilePath=new String(info.get(0) + "_" + info.get(1)+ ".doc" );
			  System.out.println(LocalfilePath);
			  try{

				      File newFile = new File (LocalfilePath);
				      		      
				      byte [] mybytearray  = new byte [(int)newFile.length()];
				      FileInputStream fis = new FileInputStream(newFile);
				      BufferedInputStream bis = new BufferedInputStream(fis);			  
				      
				      outf.initArray(mybytearray.length);
				      outf.setSize(mybytearray.length);
				      
				      bis.read(outf.getMybytearray(),0,mybytearray.length);
				      
				  	Msg reply = new Msg(outf, "211");
				
				try {
					client.sendToClient(reply);			
				} 
				catch (IOException e) {
					
					e.printStackTrace();
				}
				    
				      
				}
				catch (Exception e) {
					e.printStackTrace();
					System.out.println("Error send (Files)msg) to Server");
				}
			
		}

		/**
		 * Run student info query.
		 *
		 * @param msg the msg
		 * @param client the client
		 */
		public static void RunStudentInfoQuery(Object msg,
				ConnectionToClient client) {
				
			String Query = (String) ((Msg) msg).getMsg();
			Vector<Vector<String>> studinfo = new Vector<Vector<String>>();
			Msg reply;
			
			try {Class.forName("com.mysql.jdbc.Driver").newInstance();} 
			
			catch (Exception ex) {/* handle the error */}
			
			try {
				Connection conn = DriverManager.getConnection(
						"jdbc:mysql://localhost/aes", dblogin, dbpass);
				System.out.println("SQL connection succeed");
				
				Statement stmt = conn.createStatement();
				ResultSet rs = stmt	.executeQuery(Query);

				for(int i=0;rs.next();i++){
					studinfo.add(new Vector<String>());
					for(int j=0;j<3;j++){
						studinfo.get(i).add(rs.getString(j+1));
					}
					
				}
				reply = new Msg(studinfo,"320");
				
				try {
					client.sendToClient(reply);
					conn.close();
				} catch (IOException e) {
					
					e.printStackTrace();
				}
				
				rs.close();
			}

			catch (SQLException ex) {
				System.out.println("SQLException: " + ex.getMessage());
				System.out.println("SQLState: " + ex.getSQLState());
				System.out.println("VendorError: " + ex.getErrorCode());
			}

			
			
		}

		/**
		 * Run student stat query.
		 *
		 * @param msg the msg
		 * @param client the client
		 */
		public static void RunStudentStatQuery(Object msg,
				ConnectionToClient client) {
			
			ArrayList<Integer> grades = new ArrayList<Integer>();
			Msg reply;
			
			try {Class.forName("com.mysql.jdbc.Driver").newInstance();} 
			
			catch (Exception ex) {/* handle the error */}
			
			try {
				Connection conn = DriverManager.getConnection(
						"jdbc:mysql://localhost/aes", dblogin, dbpass);
				System.out.println("SQL connection succeed");
				
				Statement stmt = conn.createStatement();
				ResultSet rs = stmt	.executeQuery("Select Fe.Grade from finishedexam Fe where Fe.Sid = " + Integer.parseInt((String) ((Msg) msg).getMsg()));

				while(rs.next())
					grades.add(Integer.parseInt(rs.getString("Grade")));
					
				
				if(grades.isEmpty())
				{
					grades.add(200);
					reply = new Msg(grades,"321");
				}
				else
				{	
					reply = new Msg(grades,"321");
				}
				
				try {
					client.sendToClient(reply);
					conn.close();
				} catch (IOException e) {
					
					e.printStackTrace();
				}
				
				rs.close();
			}

			catch (SQLException ex) {
				System.out.println("SQLException: " + ex.getMessage());
				System.out.println("SQLState: " + ex.getSQLState());
				System.out.println("VendorError: " + ex.getErrorCode());
			}
			
		}

		/**
		 * Run teacher info query.
		 *
		 * @param msg the msg
		 * @param client the client
		 */
		public static void RunTeacherInfoQuery(Object msg,
				ConnectionToClient client) {
			String Query = (String) ((Msg) msg).getMsg();
			Vector<Vector<String>> studinfo = new Vector<Vector<String>>();
			Msg reply;
			
			try {Class.forName("com.mysql.jdbc.Driver").newInstance();} 
			
			catch (Exception ex) {/* handle the error */}
			
			try {
				Connection conn = DriverManager.getConnection(
						"jdbc:mysql://localhost/aes", dblogin, dbpass);
				System.out.println("SQL connection succeed");
				
				Statement stmt = conn.createStatement();
				ResultSet rs = stmt	.executeQuery(Query);

				for(int i=0;rs.next();i++){
					studinfo.add(new Vector<String>());
					for(int j=0;j<3;j++){
						studinfo.get(i).add(rs.getString(j+1));
					}
					
				}
				reply = new Msg(studinfo,"320");
				
				try {
					client.sendToClient(reply);
					conn.close();
				} catch (IOException e) {
					
					e.printStackTrace();
				}
				
				rs.close();
			}

			catch (SQLException ex) {
				System.out.println("SQLException: " + ex.getMessage());
				System.out.println("SQLState: " + ex.getSQLState());
				System.out.println("VendorError: " + ex.getErrorCode());
			}

			
		}

		/**
		 * Run teacher exam info query.
		 *
		 * @param msg the msg
		 * @param client the client
		 */
		public static void RunTeacherExamInfoQuery(Object msg,
				ConnectionToClient client) {
			String Query = (String) ((Msg) msg).getMsg();
			ArrayList<String> Examid = new ArrayList<String>();
			Msg reply;
			
			try {Class.forName("com.mysql.jdbc.Driver").newInstance();} 
			
			catch (Exception ex) {/* handle the error */}
			
			try {
				Connection conn = DriverManager.getConnection(
						"jdbc:mysql://localhost/aes", dblogin, dbpass);
				System.out.println("SQL connection succeed");
				
				Statement stmt = conn.createStatement();
				ResultSet rs = stmt	.executeQuery(Query);
				for(int i=0 ;rs.next();i++)
					Examid.add(rs.getString("ExamId"));
				
				
				if(Examid.isEmpty())
				{
					Examid.add("error");
					reply = new Msg(Examid,"323");
				}
				else{
					reply = new Msg(Examid,"323");
				}
			
				reply = new Msg(Examid,"323");
				
				try {
					client.sendToClient(reply);
					conn.close();
				} catch (IOException e) {
					
					e.printStackTrace();
				}
				
				rs.close();
			}

			catch (SQLException ex) {
				System.out.println("SQLException: " + ex.getMessage());
				System.out.println("SQLState: " + ex.getSQLState());
				System.out.println("VendorError: " + ex.getErrorCode());
			}
			
		}

		/**
		 * Run teacher exam grades query.
		 *
		 * @param msg the msg
		 * @param client the client
		 */
		public static void RunTeacherExamGradesQuery(Object msg,
				ConnectionToClient client) {

			ArrayList<Integer> grades = new ArrayList<Integer>();
			Msg reply;
			
			try {Class.forName("com.mysql.jdbc.Driver").newInstance();} 
			
			catch (Exception ex) {/* handle the error */}
			
			try {
				Connection conn = DriverManager.getConnection(
						"jdbc:mysql://localhost/aes", dblogin, dbpass);
				System.out.println("SQL connection succeed");
				
				Statement stmt = conn.createStatement();
				ResultSet rs = stmt	.executeQuery("Select Fe.Grade from finishedexam Fe where Fe.ExamId = "+ (String) ((Msg) msg).getMsg());

				while(rs.next())
					grades.add(Integer.parseInt(rs.getString("Grade")));
	
				reply = new Msg(grades,"321");
				
				try {
					client.sendToClient(reply);
					conn.close();
				} catch (IOException e) {
					
					e.printStackTrace();
				}
				
				rs.close();
			}

			catch (SQLException ex) {
				System.out.println("SQLException: " + ex.getMessage());
				System.out.println("SQLState: " + ex.getSQLState());
				System.out.println("VendorError: " + ex.getErrorCode());
			}
			
		}

		public static void RunCourseInfoQuery(Object msg,
				ConnectionToClient client) {
			String Query = (String) ((Msg) msg).getMsg();
			Vector<Vector<String>> studinfo = new Vector<Vector<String>>();
			Msg reply;
			
			try {Class.forName("com.mysql.jdbc.Driver").newInstance();} 
			
			catch (Exception ex) {/* handle the error */}
			
			try {
				Connection conn = DriverManager.getConnection(
						"jdbc:mysql://localhost/aes", dblogin, dbpass);
				System.out.println("SQL connection succeed");
				
				Statement stmt = conn.createStatement();
				ResultSet rs = stmt	.executeQuery(Query);

				for(int i=0;rs.next();i++){
					studinfo.add(new Vector<String>());
					for(int j=0;j<2;j++){
						studinfo.get(i).add(rs.getString(j+1));
					}
					
				}
				reply = new Msg(studinfo,"320");
				
				try {
					client.sendToClient(reply);
					conn.close();
				} catch (IOException e) {
					
					e.printStackTrace();
				}
				
				rs.close();
			}

			catch (SQLException ex) {
				System.out.println("SQLException: " + ex.getMessage());
				System.out.println("SQLState: " + ex.getSQLState());
				System.out.println("VendorError: " + ex.getErrorCode());
			}

			
		}

		public static void RunCourseExamInfoQuery(Object msg,
				ConnectionToClient client) {
			String Cid = (String) ((Msg) msg).getMsg();
			ArrayList<String> Examid = new ArrayList<String>();
			Msg reply;
			try {Class.forName("com.mysql.jdbc.Driver").newInstance();} 
			
			catch (Exception ex) {/* handle the error */}
			
			try {
				Connection conn = DriverManager.getConnection(
						"jdbc:mysql://localhost/aes", dblogin, dbpass);
				System.out.println("SQL connection succeed");
				
				Statement stmt = conn.createStatement();
				ResultSet rs = stmt	.executeQuery("Select  Fe.ExamId From finishedexam Fe");
				while(rs.next()){
					System.out.println(rs.getString("ExamId").substring(2, 4));
					if(rs.getString("ExamId").substring(2, 4).equals(Cid))
						Examid.add(rs.getString("ExamId"));
				}

				if(Examid.isEmpty())
				{
					Examid.add("error");
					reply = new Msg(Examid,"323");
				}
				else{
					reply = new Msg(Examid,"323");
				}
				try {
					client.sendToClient(reply);
					conn.close();
				} catch (IOException e) {
					
					e.printStackTrace();
				}
				
				rs.close();
			}

			catch (SQLException ex) {
				System.out.println("SQLException: " + ex.getMessage());
				System.out.println("SQLState: " + ex.getSQLState());
				System.out.println("VendorError: " + ex.getErrorCode());
			}
			
		}

		public static void RunGetExamInfo(Object msg, ConnectionToClient client) {
			String Examid = (String) ((Msg) msg).getMsg();
			ArrayList<String> info = new ArrayList<String>();
			Msg reply;
			try {Class.forName("com.mysql.jdbc.Driver").newInstance();} 
			
			catch (Exception ex) {/* handle the error */}
			
			try {
				Connection conn = DriverManager.getConnection(
						"jdbc:mysql://localhost/aes", dblogin, dbpass);
				System.out.println("SQL connection succeed");
				
				Statement stmt = conn.createStatement();
				ResultSet rs = stmt	.executeQuery("Select  E.CreationDate, E.Duration From exam E Where E.ExamId = '"+Examid+"'");
				while(rs.next()){
					info.add(rs.getString("CreationDate"));
					info.add(rs.getString("Duration"));
				}

				if(info.isEmpty())
				{
					info.add("error");
					reply = new Msg(info,"418");
				}
				else{
					reply = new Msg(info,"418");
				}
				try {
					client.sendToClient(reply);
					conn.close();
				} catch (IOException e) {
					
					e.printStackTrace();
				}
				
				rs.close();
			}

			catch (SQLException ex) {
				System.out.println("SQLException: " + ex.getMessage());
				System.out.println("SQLState: " + ex.getSQLState());
				System.out.println("VendorError: " + ex.getErrorCode());
			}
		}

}
